﻿using Microsoft.WindowsAPICodePack.Dialogs;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows;
using System.Windows.Controls;
using Tian.OperationFile.excel;

namespace software.winwpfs.develop
{
    /// <summary>
    /// CreateTableByExcel.xaml 的交互逻辑
    /// </summary>
    public partial class CreateTableByExcel : Page
    {
        public CreateTableByExcel()
        {
            InitializeComponent();
        }

        private void ButCreateSql(object sender, RoutedEventArgs e)
        {
            string tableName = TableName.Text.Trim();
            string tableKey = TableKey.Text.Trim();
            DataTable table = (ExcelTable.ItemsSource as DataView).ToTable();
            List<string> sqllist = new List<string>();
            List<string> commentlist = new List<string>();
            int dt_row_count = table.Rows.Count;
            for (int i = 0; i < dt_row_count; i++)
            {
                // 字段
                string name = table.Rows[i]["name"].ToString();
                string type = table.Rows[i]["type"].ToString();
                string annotation = table.Rows[i]["annotation"].ToString();
                string text = "";
                if (UpperCase.IsChecked == true)
                {
                    name = table.Rows[i]["name"].ToString().ToUpper();
                }
                if (LowerCase.IsChecked == true)
                {
                    name = table.Rows[i]["name"].ToString().ToLower();
                }
                text = name + " " + type;
                sqllist.Add(text);
                // 注释
                string comment = string.Format("comment on column \"{0}\".\"{1}\" is '{2}'", tableName, name, annotation);
                commentlist.Add(comment);

            }
            string sqlText = string.Format("create table {0}(\n{1}\n); \n", tableName, string.Join(",\n", sqllist));
            sqlText += string.Format("alter table {0} add constraint PK_{1}_{2} primary key ({3});\n", tableName, tableName, tableKey, tableKey);
            sqlText += string.Format("{0}", string.Join(";\n", commentlist));
            //写入文本及导出
            string lujing = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "插入语句.txt");
            File.WriteAllText(lujing, sqlText);
            MessageBoxResult messageBoxResult = HandyControl.Controls.MessageBox.Show("已生成，需要打开吗？", "打开文件", MessageBoxButton.YesNo, MessageBoxImage.Question);
            if (messageBoxResult == MessageBoxResult.Yes)
            {
                ProcessStartInfo startInfo = new ProcessStartInfo
                {
                    FileName = lujing,
                    UseShellExecute = true
                };
                Process.Start(startInfo);
            }
        }

        private void ButChooseFile(object sender, RoutedEventArgs e)
        {
            CommonOpenFileDialog dialog = new CommonOpenFileDialog("请选择一个文件夹");
            dialog.EnsureReadOnly = true;
            dialog.Filters.Add(new CommonFileDialogFilter("excel2007", "*.xlsx"));
            dialog.Filters.Add(new CommonFileDialogFilter("excel2003", "*.xls"));
            if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
            {
                if (string.IsNullOrEmpty(dialog.FileName))
                {
                    MessageBox.Show("文件夹路径不能为空", "提示");
                    return;
                }
                FirstPath.Text = dialog.FileName;
                DataTable dt = EpplusUtil.ExcelToDataTable("mysheet", FirstPath.Text);
                ExcelTable.ItemsSource = dt.DefaultView;
            }
        }

        private void UpperCase_Checked(object sender, RoutedEventArgs e)
        {

        }

        private void LowerCase_Checked(object sender, RoutedEventArgs e)
        {

        }

        private void ButAgainFile(object sender, RoutedEventArgs e)
        {
            DataTable dt = EpplusUtil.ExcelToDataTable("mysheet", FirstPath.Text);
            ExcelTable.ItemsSource = dt.DefaultView;
        }
    }
}
